﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DAL
{
    public abstract class DALBase
    {
        protected string prConnectionString;
        protected System.Data.IDbConnection prConnection;
        protected System.Data.IDbTransaction prTx;
        protected bool prAutoClose;
        protected string prEsquema;
        protected int _contadorTransacciones;
        protected bool _notCommiteable;

        private System.Collections.Generic.List<object> _EntidadesPersistidas;
        public System.Collections.Generic.List<object> EntidadesPersistidas
        {
            get { return _EntidadesPersistidas; }
        }

        protected abstract void CambiarEsquema(string Esquema);

        public DALBase()
        {
            prAutoClose = true;
            _EntidadesPersistidas = new System.Collections.Generic.List<object>();
        }

        public string Esquema
        {
            get { return prEsquema; }
            set { prEsquema = value; }
        }

        public bool AutoClose
        {
            get { return prAutoClose; }
            set { prAutoClose = value; }
        }

        public string ConnectionString
        {
            get { return prConnectionString; }
            set
            {
                if (prConnectionString != value.Trim())
                {
                    if (prConnection != null)
                    {
                        if (prConnection.State != ConnectionState.Closed)
                        {
                            throw new ApplicationException("La conexión esta abierta.");
                        }
                    }
                    prConnectionString = value.Trim();
                }
            }
        }

        /// <summary>
        /// Retorna True si la conexión es nula o su estado es cerrado
        /// </summary>
        public bool IsConnectionClosed
        {
            get
            {
                return prConnection == null || prConnection.State == ConnectionState.Closed;
            }
        }

        public System.Data.IDbConnection Connection
        {
            get
            {
                return prConnection;
            }
            set
            {
                if (prConnection != null)
                {
                    if (prConnection.State != ConnectionState.Closed)
                    {
                        throw new ApplicationException("La conexión esta abierta.");
                    }
                    if (prTx != null)
                    {
                        throw new ApplicationException("No se puede cambiar la conexion porque hay una transaccion iniciada.");
                    }
                }
                prConnection = value;
            }
        }

        public System.Data.IDbTransaction Transaction
        {
            get { return prTx; }
            set
            {
                if (prConnection != null)
                {
                    if (prConnection.State != ConnectionState.Closed)
                    {
                        if (prConnection.ConnectionString != value.Connection.ConnectionString)
                        {
                            throw new ApplicationException("La transacción corresponde a otra conexión.");
                        }
                    }
                }
                if (prTx != null)
                {
                    prTx = value;
                    prConnection = value.Connection;
                }
            }
        }

        public void Open()
        {
            if (prConnection == null)
            {
                prConnection = NewConnection();
            }
            if (prConnection.State == ConnectionState.Closed)
            {
                prConnection.ConnectionString = prConnectionString;
                try
                {
                    prConnection.Open();
                    if (!String.IsNullOrEmpty(prEsquema))
                    {
                        CambiarEsquema(prEsquema);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public void Close()
        {
            if (prTx != null)
            {
                throw new ApplicationException("No se puede cerrar la conexión hasta que no finalice la transacción pendiente.");
            }
            if (prConnection.State != ConnectionState.Closed)
            {
                prConnection.Close();
            }
        }

        #region "Command"

        protected abstract IDbCommand GetCachedCommand(string CommandText);
        //Debe devolver nothing o un command ya configurado
        protected abstract void CacheCommand(IDbCommand cmd, object[] Parameters);
        protected abstract void ClearCacheCommand();

        public abstract IDbCommand NewCommand();
        public abstract IDbConnection NewConnection();
        public abstract IDbDataParameter NewParameter();

        public abstract void DeriveParameters(IDbCommand cmd);

        public IDbCommand BuildCommand(string SpName, object[] Parameters)
        {
            return BuildCommand(SpName, CommandType.StoredProcedure, Parameters);
        }

        public virtual IDbCommand BuildCommand(string CommandText, CommandType CommandType, object[] Parameters)
        {
            IDbCommand cmd = GetCachedCommand(CommandText);
            try
            {
                if (cmd == null)
                {
                    cmd = NewCommand();
                    cmd.CommandText = CommandText;
                    cmd.CommandType = CommandType;
                    if (CommandType == CommandType.StoredProcedure)
                    {
                        IDbConnection CnAux = NewConnection();
                        CnAux.ConnectionString = prConnectionString;
                        CnAux.Open();
                        cmd.Connection = CnAux;
                        DeriveParameters(cmd);
                        CnAux.Close();
                        CnAux.Dispose();
                    }
                }
                if (CommandType == CommandType.StoredProcedure)
                {
                    int F = 0;
                    int J = 0;
                    //Si es SQL server el primer parametro es el returnvalue
                    if (cmd.Parameters.Count > 0)
                    {
                        if (((IDataParameter)cmd.Parameters[0]).Direction == ParameterDirection.ReturnValue)
                        {
                            J = 1;
                        }
                    }
                    if (Parameters != null)
                    {
                        for (F = 0; F < Parameters.GetLength(0); F++)
                        {
                            if (cmd.Parameters.Count == F + J) break;
                            if (Parameters[F] == null)
                            {
                                ((IDataParameter)cmd.Parameters[F + J]).Value = DBNull.Value;
                            }
                            else
                            {
                                ((IDataParameter)cmd.Parameters[F + J]).Value = Parameters[F];
                            }
                        }
                    }
                    CacheCommand(cmd, Parameters);
                }

                cmd.Connection = prConnection;
                cmd.Transaction = prTx;
                return cmd;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region "DataAdapter"

        public abstract IDataAdapter NewDataAdapter();
        public abstract void DataAdapterFill(IDbCommand cmd, DataTable dt);
        public abstract void DataAdapterFill(IDbCommand cmd, DataSet ds);
        public abstract void DataAdapterFill(IDbCommand cmd, DataSet ds, string SourceTable);
        public abstract void DataAdapterFill(System.Data.IDbCommand cmd, System.Data.DataSet ds, int startRecord, int maxRecords, string SourceTable);

        #endregion

        #region "Fill"

        public void Fill(ref DataSet Ds, string DataTableName, string spName)
        {
            Fill(ref Ds, DataTableName, spName, CommandType.StoredProcedure, null);
        }

        public void Fill(ref DataSet Ds, string DataTableName, string spName, object[] Parameters)
        {
            Fill(ref Ds, DataTableName, spName, CommandType.StoredProcedure, Parameters);
        }

        public void Fill(ref DataSet Ds, string DataTableName, string CommandText, System.Data.CommandType CommandType, object[] Parameters)
        {
            System.Data.IDbCommand cmd;
            //Abro la conexion, si ya esta abierta no hace nada
            this.Open();
            try
            {
                //Esta rutina configura el command y le carga los parametros
                cmd = BuildCommand(CommandText, CommandType, Parameters);
                try
                {
                    DataAdapterFill(cmd, Ds, DataTableName);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                try
                {
                    RetornarParametrosDeSalida(ref cmd, ref Parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    //Si la clase tiene autoclose = true cierro la conexión
                    if (prAutoClose && prTx == null)
                    {
                        this.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public void Fill(ref DataSet Ds, string DataTableName, Int32 startRecord, Int32 maxRecords, string CommandText, System.Data.CommandType CommandType, object[] Parameters)
        {
            System.Data.IDbCommand cmd;
            //Abro la conexion, si ya esta abierta no hace nada
            this.Open();
            try
            {
                //Esta rutina configura el command y le carga los parametros
                cmd = BuildCommand(CommandText, CommandType, Parameters);
                try
                {
                    DataAdapterFill(cmd, Ds, startRecord, maxRecords, DataTableName);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                try
                {
                    RetornarParametrosDeSalida(ref cmd, ref Parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    //Si la clase tiene autoclose = true cierro la conexión
                    if (prAutoClose && prTx == null)
                    {
                        this.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public void Fill(DataTable Dt, string spName)
        {
            Fill(Dt, spName, CommandType.StoredProcedure, null);
        }

        public void Fill(DataTable Dt, string spName, object[] Parameters)
        {
            Fill(Dt, spName, CommandType.StoredProcedure, Parameters);
        }

        public void Fill(DataTable Dt, string CommandText, System.Data.CommandType CommandType, object[] Parameters)
        {
            System.Data.IDbCommand cmd;
            //Abro la conexion, si ya esta abierta no hace nada
            try
            {
                this.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            try
            {
                try
                {
                    //Esta rutina configura el command y le carga los parametros
                    cmd = BuildCommand(CommandText, CommandType, Parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                try
                {
                    DataAdapterFill(cmd, Dt);
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                try
                {
                    RetornarParametrosDeSalida(ref cmd, ref Parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                try
                {
                    //Si la clase tiene autoclose = true cierro la conexión
                    if (prAutoClose && prTx == null) { this.Close(); }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        #endregion

        #region "ExecuteDataset"

        public DataSet ExecuteDataset(string spName)
        {
            return this.ExecuteDataset(spName, CommandType.StoredProcedure, "Table1", null);
        }

        public DataSet ExecuteDataset(string spName, object[] Parameters)
        {
            return this.ExecuteDataset(spName, CommandType.StoredProcedure, "Table1", Parameters);
        }

        public DataSet ExecuteDataset(string spName, string DataSetTableName, object[] Parameters)
        {
            return this.ExecuteDataset(spName, CommandType.StoredProcedure, DataSetTableName, Parameters);
        }

        //Este metodo executa un comando que retorna filas y las devuelve en un dataset.
        //Usar los objetos especificos del proveedor
        public DataSet ExecuteDataset(string CommandText, System.Data.CommandType CommandType, string DataSetTableName, object[] Parameters)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            Fill(ref ds, DataSetTableName, CommandText, CommandType, Parameters);
            return ds;
        }

        //Este metodo executa un comando que retorna filas y las devuelve en un dataset.
        //Usar los objetos especificos del proveedor.
        //Ademas permite especificarle que retorno solo un guropo de filas a travez de 
        //los parametros FromRecord y NumRecords. 
        //A diferencia del metodo anterior este no usa un dataapater, sino que realiza 
        //el Fill manualmente con un datareader
        public DataSet ExecuteDataset(string CommandText, System.Data.CommandType CommandType, string DataSetTableName, Int32 FromRecord, Int32 NumRecords, params object[] Parameters)
        {
            System.Data.IDbCommand cmd = null;
            DataSet ds = new DataSet();
            System.Data.IDataReader dr;
            System.Data.DataTable dt;
            System.Data.DataRow Row;
            System.Data.DataColumn Column;
            System.Data.DataTable dtReturn;
            try
            {
                this.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            try
            {
                //Esta rutina configura el command y le carga los parametros
                cmd = BuildCommand(CommandText, CommandType, Parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            try
            {
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //Recorro el reader. Este DO recorre los resulsets del reader porque el
            //command puede retornar mas de 1 SELECT
            do
            {
                //Creo la tabla para recibir las filas en el DataSet.
                //La primera tabla toma el nombre del parametro, las siguientes llevan
                //un numero correlativa
                if (ds.Tables.Count == 0)
                {
                    ds.Tables.Add(DataSetTableName);
                }
                else
                {
                    ds.Tables.Add(DataSetTableName + ds.Tables.Count);
                }
                //dtReturn es donde pongo las Filas
                dtReturn = ds.Tables[ds.Tables.Count - 1];

                //Si el datatable ya tiene columnas las dejo, sino creo las columnas
                if (dtReturn.Columns.Count == 0)
                {
                    //Obtengo en dt las columnas de la consulta
                    dt = dr.GetSchemaTable();
                    try
                    {
                        //Recorro la tabla del Schema para ir armando la tabla de retorno
                        foreach (System.Data.DataRow _row in dt.Rows)
                        {
                            //Agrego y configuro la columna
                            dtReturn.Columns.Add(_row[0].ToString(), _row[0].GetType());                            

                            Column = dtReturn.Columns[(string)_row[0]];
                            try
                            {
                                Column.MaxLength = ((Int32)_row["ColumnSize"]);
                            }
                            catch { }
                            Column.ReadOnly = ((bool)_row["IsReadOnly"]);
                            Column.Unique = ((bool)_row["IsUnique"]);
                            Column.AutoIncrement = ((bool)_row["IsAutoIncrement"]);
                            Column.AllowDBNull = ((bool)_row["AllowDBNull"]);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                } //Fin creación de Schema

                //En esta matriz leo la Fila completa para cargarla en dtReturn
                object[] Fieldsvalues = new object[(dtReturn.Columns.Count - 1 + 1)];
                bool bMoreRecords;
                //Si FromRecord  > 0 avanzo en el reader hasta llegar al reg. o al final de reader
                do
                {
                    FromRecord -= 1;
                    bMoreRecords = dr.Read();
                }
                while (!(FromRecord <= 0 | bMoreRecords == false));
                if (NumRecords < 0)
                {
                    throw new ApplicationException("NumRecords must be greater than 0");
                }
                //Si especifico una cantidad de registros sino lo asigno a un numero grande
                if (NumRecords == 0)
                {
                    NumRecords = 9999999;
                }
                if (bMoreRecords)
                {
                    do
                    {
                        //leo el registro en la matriz de valores
                        dr.GetValues(Fieldsvalues);
                        //Inserto la fila en el datatable
                        dtReturn.Rows.Add(Fieldsvalues);
                        NumRecords -= 1;
                    }
                    while (!(dr.Read() == false || NumRecords == 0));
                }
            }
            while (!(dr.NextResult() == false));
            dr.Close();

            try
            {
                RetornarParametrosDeSalida(ref cmd, ref Parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (prAutoClose && prTx == null)
            {
                try
                {
                    this.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return ds;
        }

        #endregion

        #region "ExecuteNonQuery"

        public Int32 ExecuteNonQuery(string SpName)
        {
            return this.ExecuteNonQuery(SpName, CommandType.StoredProcedure, null);
        }

        public Int32 ExecuteNonQuery(string SpName, object[] Parameters)
        {
            return this.ExecuteNonQuery(SpName, CommandType.StoredProcedure, Parameters);
        }

        public Int32 ExecuteNonQuery(string CommandText, CommandType CommandType, object[] Parameters)
        {

            System.Data.IDbCommand cmd = NewCommand();
            Int32 retval;

            //Abro la conexion, si ya esta abierta no hace nada
            try
            {
                this.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            try
            {
                //Esta rutina configura el command y le carga los parametros
                cmd = BuildCommand(CommandText, CommandType, Parameters);
            }
            catch (Exception ex)
            {
                if (prAutoClose && prTx == null)
                    Close();
                throw ex;
            }

            try
            {
                //Ejecuto el comando
                retval = cmd.ExecuteNonQuery();
            }

            catch (Exception ex)
            {
                Logueo.LogueaError(ex, "DALBase.ExecuteNonQuery", cmd.CommandText, Parameters);
                if (prAutoClose && prTx == null)
                    Close();
                throw ex;
            }
            try
            {
                RetornarParametrosDeSalida(ref cmd, ref Parameters);
            }
            catch (Exception ex)
            {
                if (prAutoClose && prTx == null)
                    Close();
                throw ex;
            }

            if (prAutoClose && prTx == null)
            {
                this.Close();
            }
            return retval;
        }

        #endregion

        #region "ExecuteScalar"

        public object ExecuteScalar(string SpName)
        {
            return this.ExecuteScalar(SpName, CommandType.StoredProcedure, null);
        }

        public object ExecuteScalar(string SpName, object[] Parameters)
        {
            return this.ExecuteScalar(SpName, CommandType.StoredProcedure, Parameters);
        }

        public object ExecuteScalar(string CommandText, CommandType CommandType, object[] Parameters)
        {
            System.Data.IDbCommand cmd = NewCommand();
            object retval;

            //Abro la conexion, si ya esta abierta no hace nada
            this.Open();

            //Esta rutina configura el command y le carga los parametros
            cmd = BuildCommand(CommandText, CommandType, Parameters);

            try
            {
                //Ejecuto el command
                retval = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            try
            {
                RetornarParametrosDeSalida(ref cmd, ref Parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //Si la clase tiene autoclose = true cierro la conexión
            if (prAutoClose && prTx == null)
            {
                this.Close();
            }

            return retval;

        }

        #endregion

        #region "ExecuteReader"
        //Retorna un reader para darle mas flexibilidad y no obligar a traer todas las filas
        //El reader es el especifico del provider, pero devuelve la interfaz generica
        public System.Data.IDataReader ExecuteReader(string SpName)
        {
            return this.ExecuteReader(SpName, CommandType.StoredProcedure, null);
        }

        public System.Data.IDataReader ExecuteReader(string SpName, object[] Parameters)
        {
            return this.ExecuteReader(SpName, CommandType.StoredProcedure, Parameters);
        }

        public System.Data.IDataReader ExecuteReader(string CommandText, CommandType CommandType, object[] Parameters)
        {

            System.Data.IDbCommand cmd = NewCommand();
            System.Data.IDataReader dr;
            try
            {
                this.Open();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            try
            {
                //Esta rutina configura el command y le carga los parametros
                cmd = BuildCommand(CommandText, CommandType, Parameters);
            }
            catch (Exception ex)
            {
                if (prAutoClose && prTx == null)
                    Close();
                throw ex;
            }
            try
            {
                //Ejecuta el command
                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Logueo.LogueaError(ex, "DALBase.ExecuteReader", cmd.CommandText, Parameters);
                if (prAutoClose && prTx == null)
                    Close();
                throw ex;
            }
            //RetornarParametrosDeSalida(cmd, Parameters) 
            return dr;
        }

        #endregion

        #region "BeginsTrans"

        public void BeginTrans()
        {
            if (prConnection == null || prConnection.State != ConnectionState.Open)
            {
                this.Open();
            }
            if (prTx == null)
            {
                _notCommiteable = false;
                prTx = prConnection.BeginTransaction();
                _EntidadesPersistidas.Clear();
                _contadorTransacciones = 1;
            }
            else
            {
                _contadorTransacciones += 1;
            }
        }
        #endregion

        #region "CommitTrans"
        public void CommitTrans()
        {
            if (prTx == null)
            {
                throw new ArgumentException("Debe abrir iniciar una transaccion antes de llamar a CommitTrans.");
            }
            _contadorTransacciones -= 1;
            if (_contadorTransacciones == 0)
            {
                if (_notCommiteable == true)
                {
                    _contadorTransacciones += 1;
                    throw new ApplicationException("No se puede hacer commit porque se llamo a rollback en unta transaccion anidada.");
                }
                prTx.Commit();
                if (CommitedEvent != null) CommitedEvent(this, _EntidadesPersistidas);

                _EntidadesPersistidas.Clear();

                prTx = null;
                if (prAutoClose)
                {
                    prConnection.Close();
                }
            }
        }
        #endregion

        #region "RollBack"
        public void RollBack()
        {
            if (prTx == null)
            {
                throw new ArgumentException("Debe abrir iniciar una transaccion antes de llamar a CommitTrans.");
            }
            _notCommiteable = true;
            _contadorTransacciones -= 1;
            if (_contadorTransacciones == 0)
            {
                prTx.Rollback();
                _EntidadesPersistidas.Clear();
                prTx = null;
                if (prAutoClose)
                {
                    prConnection.Close();
                }
            }
        }
        #endregion

        #region "Privadas"

        private void RetornarParametrosDeSalida(ref IDbCommand Cmd, ref object[] Parameters)
        {
            int J = 0;
            if (Parameters == null) return;
            //Si es SQL server el primer parametro es el returnvalue
            if (Cmd.Parameters.Count > 0)
            {
                if (((IDataParameter)(Cmd.Parameters[0])).Direction == ParameterDirection.ReturnValue)
                {
                    J = 1;
                }
            }
            for (int f = 0 + J; f < Cmd.Parameters.Count; f++)
            {
                if (f > Parameters.GetLength(0) - 1)
                {
                    break;
                }
                if (((IDataParameter)(Cmd.Parameters[0])).Direction != ParameterDirection.Input)
                {
                    Parameters[f - J] = ((IDbDataParameter)Cmd.Parameters[f]).Value;
                }
            }
        }

        #endregion

        #region "Caché de entidades"

        public event CommitedHandler CommitedEvent;
        public delegate void CommitedHandler(DALBase oDalBase, System.Collections.Generic.List<object> e);

        public void AddPersitedObject(object entidad)
        {
            _EntidadesPersistidas.Add(entidad);
        }

        #endregion
    }
}
